﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class PhieukhamDAL : BaseDAL, UTL.IBaseDAL 
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @"SELECT *,(Case when baohiem = 0 then N'Không có bảo hiểm' else N'Có bảo hiểm' end) as BH,(Case when trangthaithanhtoan = 0 then N'Chưa lập phiếu thanh toán' else N'Đã lập phiếu thanh toán' end) as TTTT FROM phieukham a join trangthaikham b on a.matrangthai = b.matrangthai join nhanvien c on a.ma_nv=c.ma_nv join phong d on a.sophong=d.sophong and a.makhoa=d.makhoa join benhnhan e on a.ma_bn=e.ma_bn join KHOA f on d.MAKHOA=f.MAKHOA";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool deleteobj(object obj)
        {
            var o = (phieukham)obj;
            string sql = @"DELETE FROM phieukham WHERE sttphieu = '{0}' and ngaykham='{1}'";
            sql = string.Format(sql, o.sttphieu, o.ngaykham);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();
        }

        public bool Insert(object obj)
        {
            var o = (phieukham)obj;
            string sql = @"set dateformat mdy; INSERT INTO phieukham(sttphieu,ngaykham,baohiem,phikham,ketluanbenh,matrangthai,ma_nv,sophong,makhoa,ma_bn,trangthaithanhtoan) VALUES('{0}','{1}','{2}',{3},N'{4}','{5}','{6}','{7}','{8}','{9}','{10}') ";
            sql = string.Format(sql,o.sttphieu,o.ngaykham,o.baohiem,o.phikham,o.ketluanbenh,o.matrangthai,o.manv,o.sophong,o.makhoa,o.mabn,o.trangthaithanhtoan);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (phieukham)obj;
            string sql = @"set dateformat mdy; UPDATE phieukham SET baohiem='{0}', phikham={1}, ketluanbenh=N'{2}', matrangthai='{3}', ma_nv='{4}', sophong={5}, makhoa='{6}', ma_bn='{7}',trangthaithanhtoan='{8}' WHERE sttphieu='{9}' and ngaykham='{10}' ";
            sql = string.Format(sql, o.baohiem, o.phikham, o.ketluanbenh, o.matrangthai, o.manv, o.sophong, o.makhoa, o.mabn, o.trangthaithanhtoan,o.sttphieu, o.ngaykham);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_benhnhan()
        {
            string sql = @"SELECT ma_bn as id, hoten_bn as ten FROM benhnhan";
            return ExecuteQuery(sql);
        }
        public DataTable Select_nhanvien()
        {
            string sql = @" SELECT ma_nv as id, ten_nv as ten FROM nhanvien";
            return ExecuteQuery(sql);
        }
        public DataTable Select_trangthaikham()
        {
            string sql = @" SELECT matrangthai as id, tentrangthai as ten FROM trangthaikham";
            return ExecuteQuery(sql);
        }
        
        public DataTable Select_phong()
        {
            string sql = @" SELECT sophong as id, tenphong as ten FROM phong";
            return ExecuteQuery(sql);
        }

        public DataTable Select_khoa()
        {
            string sql = @" SELECT makhoa as id, tenkhoa as ten FROM khoa";
            return ExecuteQuery(sql);
        }
        
        public DataTable Select_phongtheokhoa(string makhoa)
        {
            string sql = @" SELECT sophong as id, tenphong as ten FROM phong where makhoa='{0}'";
            sql = string.Format(sql,makhoa);
            return ExecuteQuery(sql);
        }

        public DataTable Select_Report(string id, DateTime ngay)
        {
            string sql = @" select STTPHIEU as STTPhieu,HOTEN_BN as HotenBN ,(case when GIOITINH_BN = 1 then 'Nam' else N'Nữ' end) as GioitinhBN,NGAYKHAM as Ngaykham, TENPHONG as Phongkham,TENKHOA as Khoa,KETLUANBENH
                            from PHIEUKHAM a join BENHNHAN b on a.MA_BN =b.MA_BN
                                join PHONG c on a.SOPHONG = c.SOPHONG and a.MAKHOA = c.MAKHOA 
                                join KHOA d on c.MAKHOA =d.MAKHOA 
                            where STTPHIEU='{0}' and NGAYKHAM ='{1}';";
            sql = string.Format(sql, id, ngay);
            return ExecuteQuery(sql);
        
        }

        public DataTable Select_Reporttiepnhanbenh(string id, DateTime ngay)
        {
            string sql = @" select STTPHIEU as STTPhieu,HOTEN_BN as HotenBN ,(case when GIOITINH_BN = 1 then 'Nam' else N'Nữ' end) as GioitinhBN,NGAYKHAM as Ngaykham, TENPHONG as Phongkham,TENKHOA as Khoa,PHIKHAM,TENPHONG,TEN_NV
                            from PHIEUKHAM a join BENHNHAN b on a.MA_BN =b.MA_BN
                                join PHONG c on a.SOPHONG = c.SOPHONG and a.MAKHOA = c.MAKHOA 
                                join KHOA d on c.MAKHOA =d.MAKHOA 
                                join NHANVIEN e on a.MA_NV = E.MA_NV   
                            where STTPHIEU='{0}' and NGAYKHAM ='{1}';";
            sql = string.Format(sql, id, ngay);
            return ExecuteQuery(sql);

        }

        public bool Update_bacsi(object obj)
        {
            var o = (phieukham)obj;
            string sql = @"set dateformat mdy; UPDATE phieukham SET ketluanbenh=N'{0}', matrangthai='{1}' WHERE sttphieu='{2}' and ngaykham='{3}' ";
            sql = string.Format(sql,o.ketluanbenh, o.matrangthai, o.sttphieu, o.ngaykham);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
        /// <summary>
        /// lay ...
        /// </summary>
        /// <returns></returns>
        //public string Select_max()
        //{
        //    string sql = @"SELECT max(ma_bn) as id FROM benhnhan";
        //    DataTable tb= ExecuteQuery(sql);
            
        //    return tb.Rows.Count>0? tb.Rows[0]["id"]+"":"0";
        //}



        
    }
}
